package com.baseweb.webadmin.core.util;

import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.xssf.streaming.SXSSFCell;
import org.apache.poi.xssf.streaming.SXSSFRow;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.List;

public class ExcelUtil {
    private static Logger logger = LoggerFactory.getLogger(ExcelUtil.class);
    private static final int EXCEL_SHEET_DATASIZE = 65530;

    /**
     * 在输出流中导出excel
     *
     * @param excelName 导出的excel名称 包括扩展名
     * @param sheetName 导出的sheet名称
     * @param fieldName 列名数组
     * @param data      数据组
     * @param response  response
     */
    public static void makeStreamExcel(String excelName, String sheetName, String[] fieldName
            , List<String[]> data, javax.servlet.http.HttpServletResponse response) {
        OutputStream os = null;
        try {
            response.reset(); // 清空输出流
            os = response.getOutputStream(); // 取得输出流
            response.setHeader("Content-disposition", "attachment; filename="
                    + new String(excelName.getBytes(), "ISO-8859-1")); // 设定输出文件头
            response.setContentType("application/msexcel"); // 定义输出类型
        } catch (IOException ex) {// 捕捉异常
//            System.out.println("流操作错误:" + ex.getMessage());
        }
        //在内存中生成工作薄
        SXSSFWorkbook workbook = makeWorkBook(sheetName, fieldName, data);
        try {
//            os.flush();
            workbook.write(os);
            os.close();
        } catch (IOException e) {
//            e.printStackTrace();
//            System.out.println("Output is closed");
        }
    }

    public static void makeFileExcel(String excelName, String sheetName, String[] fieldName
            , List<String[]> data) {
        OutputStream os = null;
        try {
            os = new FileOutputStream("/home/static_data/excelexport/" + excelName + ".xlsx");
        } catch (FileNotFoundException e) {
            e.printStackTrace();  //To change body of catch statement use File | Settings | File Templates.
        }

        //在内存中生成工作薄
        SXSSFWorkbook workbook = makeWorkBook(sheetName, fieldName, data);
        try {
//            os.flush();
            workbook.write(os);
            os.close();
        } catch (IOException e) {
//            e.printStackTrace();
//            System.out.println("Output is closed");
        }
    }

    public static void makeFileExcel(String excelName, String sheetName, String[] fieldName
            , List<String[]> data, String addr) {
        OutputStream os = null;
        try {
            os = new FileOutputStream(addr + excelName + ".xlsx");
        } catch (FileNotFoundException e) {
            e.printStackTrace();  //To change body of catch statement use File | Settings | File Templates.
        }

        //在内存中生成工作薄
        SXSSFWorkbook workbook = makeWorkBook(sheetName, fieldName, data);
        try {
//            os.flush();
            workbook.write(os);
            os.close();
        } catch (IOException e) {
//            e.printStackTrace();
//            System.out.println("Output is closed");
        }
    }

    private static void processExcelTitle(String[] fieldName, SXSSFSheet sheet) {
        SXSSFRow row = (SXSSFRow) sheet.createRow(0);
        SXSSFCell cell;
        for (int i = 0; i < fieldName.length; i++) {
            // 创建第一行各个字段名称的单元格
            cell = (SXSSFCell) row.createCell((short) i);
            // 设置单元格内容为字符串型
            cell.setCellType(SXSSFCell.CELL_TYPE_STRING);
            // 为了能在单元格中输入中文,设置字符集为UTF_16
            // cell.setEncoding(HSSFCell.ENCODING_UTF_16);
            // 给单元格内容赋值
            cell.setCellValue(new HSSFRichTextString(fieldName[i]));
        }
    }


    /**
     * 根据条件，生成工作薄对象到内存
     *
     * @param sheetName 工作表对象名称
     * @param fieldName 首列列名称
     * @param data      数据
     * @return HSSFWorkbook
     */
    public static SXSSFWorkbook makeWorkBook(String sheetName, String[] fieldName
            , List<String[]> data) {
        // 产生工作薄对象
        SXSSFWorkbook workbook = new SXSSFWorkbook();
        // 产生工作表对象
        SXSSFSheet sheet = (SXSSFSheet) workbook.createSheet();
        // 为了工作表能支持中文,设置字符集为UTF_16
        workbook.setSheetName(0, sheetName);
        int num = 1;
        // 产生一行
        //HSSFRow row = sheet.createRow(0);
        // 产生单元格
        SXSSFCell cell;
        // 写入各条记录,每条记录对应excel表中的一行
        for (int i = 0; i < data.size(); i++) {

            int sheetLastSize = sheet.getLastRowNum();
            if (sheetLastSize >= EXCEL_SHEET_DATASIZE) {

                sheet = (SXSSFSheet) workbook.createSheet();
                workbook.setSheetName(num, sheetName + "(" + num + ")");
                num++;
                sheetLastSize = sheet.getLastRowNum();

                // 处理标题头
                // 写入各个字段的名称
                processExcelTitle(fieldName, sheet);
            } else if (i == 0) {
                processExcelTitle(fieldName, sheet);
            }
            SXSSFRow row = (SXSSFRow) sheet.createRow(sheetLastSize + 1);

            String[] tmp = data.get(i);
            // 生成一行
//            row = sheet.createRow(i + 1);
            for (int j = 0; j < tmp.length; j++) {
                cell = (SXSSFCell) row.createCell((short) j);
                //设置单元格字符类型为String
                cell.setCellType(SXSSFCell.CELL_TYPE_STRING);
                cell.setCellValue(new String((tmp[j] == null) ? "" : tmp[j]));
            }
        }
        return workbook;
    }

}
